library(DBI)
library(kableExtra)
library(ggplot2)
library(dplyr)
library(knitr)
library(DT)


# Establish MySQL database connection
con <- dbConnect(
  RMySQL::MySQL(),
  dbname = "practicum2",
  host = "database-2.cr84qqy6ksk7.us-east-2.rds.amazonaws.com", 
  port = 3306, 
  user = "admin",
  password = "practicum2"
)

# Checking if the connection has been successfully established
if (dbIsValid(con)) {
  cat("Connection to MySQL database successful!\n")
} else {
  cat("Failed to connect to the MySQL database.\n")
}
Connection to MySQL database successful!

###Analytical Query I: Top five sales reps with the most sales broken down by year. So, for each year for which there is data, the top five reps for that year. Think about how you would best present this information so it is easily usable by an analyst or manager

query1 <- "
WITH ranked_reps AS (
    SELECT 
        year,
        repName,
        SUM(totalSold) AS total_sales,
        ROW_NUMBER() OVER (PARTITION BY year ORDER BY SUM(totalSold) DESC) AS sales_rank
    FROM 
        rep_facts 
    GROUP BY 
        year, repName
)
SELECT 
    year,
    repName,
    total_sales
FROM 
    ranked_reps
WHERE 
    sales_rank <= 5
ORDER BY 
    year, total_sales DESC;

"
result1 <- dbGetQuery(con, query1)
Warning: Decimal MySQL column 2 imported as numeric
# Formatting the data for presentation
 datatable(result1, 
          options = list(
            stripeClasses = c('odd', 'even'),  # Apply alternating row colors
            paging = TRUE,                      # Enable pagination if needed
            searching = TRUE                    # Enable search functionality
          )
)
NA
NA

###Analytical Query II: Total sold per product per quarter. Think about how to best display this information.

AQ2 <- "
SELECT 
    productName,
    quarter,
    SUM(totalUnitsSold) AS totalUnitsSold
FROM product_facts 
GROUP BY productName, quarter
ORDER BY
    productName,
    quarter;
"
# Execute the query and store the result
resultII <- dbGetQuery(con, AQ2)
Warning: Decimal MySQL column 2 imported as numeric
# Arrange the result by productName, and quarter
resultII <- arrange(resultII, productName, quarter)

datatable(resultII, 
          options = list(
            paging = TRUE,         # Enable pagination
            searching = TRUE,      # Enable search functionality
            ordering = TRUE       # Enable column sorting
          ),
          caption = "Average Sales per Sales Rep Over the Years"
)
NA
NA
NA

Analytical Query III: Number of units sold per product per region. Show the result in a line graph visualization


# Execute the query and store the result
AQ3 <- "
SELECT 
    productName,
    territory,
    SUM(totalUnitsSold) AS totalUnitsSold
FROM product_facts 
GROUP BY productName, territory
"
resultIII <- dbGetQuery(con, AQ3)

# Arrange the result 
resultIII <- arrange(resultIII, productName, territory)

# Formatting the data for presentation
result <- resultIII %>%
  kable("html") %>%
  kable_styling(bootstrap_options = "striped", full_width = FALSE)

result
productName territory totalUnitsSold
Alaraphosol EMEA 510600
Alaraphosol East 296500
Alaraphosol South America 475500
Alaraphosol West 294900
Bhiktarvizem EMEA 467100
Bhiktarvizem East 351600
Bhiktarvizem South America 480300
Bhiktarvizem West 351300
Clobromizen EMEA 478600
Clobromizen East 355300
Clobromizen South America 486600
Clobromizen West 392400
Colophrazen EMEA 533500
Colophrazen East 353000
Colophrazen South America 493100
Colophrazen West 331000
Diaprogenix EMEA 506400
Diaprogenix East 312900
Diaprogenix South America 534000
Diaprogenix West 350700
Gerantrazeophem EMEA 524900
Gerantrazeophem East 383500
Gerantrazeophem South America 534000
Gerantrazeophem West 329600
Presterone EMEA 499000
Presterone East 324700
Presterone South America 470800
Presterone West 337700
Proxinostat EMEA 500800
Proxinostat East 360200
Proxinostat South America 522000
Proxinostat West 388500
Xinoprozen EMEA 498400
Xinoprozen East 324700
Xinoprozen South America 506800
Xinoprozen West 304800
Xipramin EMEA 225500
Xipramin East 188300
Xipramin South America 275700
Xipramin West 164400
Zalofen EMEA 274200
Zalofen East 153100
Zalofen South America 254400
Zalofen West 165900


library(plotly)

# Assuming 'result3' is your data frame containing the query result

# Create the line plot
line_plot <- plot_ly(resultIII, x = ~territory, y = ~totalUnitsSold, color = ~productName, type = 'scatter', mode = 'lines+markers') %>%
  layout(title = "Total Units Sold by Territory",
         xaxis = list(title = "Territory"),
         yaxis = list(title = "Total Units Sold"),
         legend = list(orientation = "h"))

# Print the line plot
line_plot
NA

###Analytical Query IV: Average sales per sales rep over the years. Show the result in a line graph visualization.

library(DT)
# Execute the SQL query and fetch the result
AQ4 <- "
SELECT 
    rf.repName,
    rf.year,
    AVG(rf.totalSold) AS avg_sales
FROM 
    rep_facts rf
JOIN 
    product_facts pf ON rf.productID = pf.productID
GROUP BY 
    rf.repName, rf.year;
"
resultIV <- dbGetQuery(con, AQ4)
Warning: Decimal MySQL column 2 imported as numeric
# Display the result in table format
datatable(resultIV, 
          options = list(
            paging = TRUE,         # Enable pagination
            searching = TRUE,      # Enable search functionality
            ordering = TRUE       # Enable column sorting
          ),
          caption = "Average Sales per Sales Rep Over the Years"
)
library(ggplot2)

# Plot the line graph
ggplot(resultIV, aes(x = year, y = avg_sales, group = repName, color = repName)) +
  geom_line() +
  geom_point() +
  labs(title = "Average Sales per Sales Rep Over the Years",
       x = "Year",
       y = "Average Sales") +
  theme_minimal() +
  theme(legend.position = "bottom") +
  scale_color_viridis_d()


dbDisconnect(con)
[1] TRUE

Conclusion

This report provides valuable insights into our sales performance, highlighting trends and patterns that can inform strategic decision-making. By understanding our sales data more comprehensively, we can identify opportunities for growth and optimize our sales strategies.

LS0tDQp0aXRsZTogIkV4cGxvcmUgYW5kIE1pbmUgRGF0YSINCmF1dGhvcjogIlByYXRoYW1lc2ggamFnZGFsZSINCmRhdGU6ICJTcHJpbmcyMDI0Ig0Kb3V0cHV0OiBodG1sX25vdGVib29rDQotLS0NCg0KYGBge3IgQ29ubmVjdGluZyB0byB0aGUgZGF0YWJhc2V9DQpsaWJyYXJ5KERCSSkNCmxpYnJhcnkoa2FibGVFeHRyYSkNCmxpYnJhcnkoZ2dwbG90MikNCmxpYnJhcnkoZHBseXIpDQpsaWJyYXJ5KGtuaXRyKQ0KbGlicmFyeShEVCkNCg0KDQojIEVzdGFibGlzaCBNeVNRTCBkYXRhYmFzZSBjb25uZWN0aW9uDQpjb24gPC0gZGJDb25uZWN0KA0KICBSTXlTUUw6Ok15U1FMKCksDQogIGRibmFtZSA9ICJwcmFjdGljdW0yIiwNCiAgaG9zdCA9ICJkYXRhYmFzZS0yLmNyODRxcXk2a3NrNy51cy1lYXN0LTIucmRzLmFtYXpvbmF3cy5jb20iLCANCiAgcG9ydCA9IDMzMDYsIA0KICB1c2VyID0gImFkbWluIiwNCiAgcGFzc3dvcmQgPSAicHJhY3RpY3VtMiINCikNCg0KIyBDaGVja2luZyBpZiB0aGUgY29ubmVjdGlvbiBoYXMgYmVlbiBzdWNjZXNzZnVsbHkgZXN0YWJsaXNoZWQNCmlmIChkYklzVmFsaWQoY29uKSkgew0KICBjYXQoIkNvbm5lY3Rpb24gdG8gTXlTUUwgZGF0YWJhc2Ugc3VjY2Vzc2Z1bCFcbiIpDQp9IGVsc2Ugew0KICBjYXQoIkZhaWxlZCB0byBjb25uZWN0IHRvIHRoZSBNeVNRTCBkYXRhYmFzZS5cbiIpDQp9DQpgYGANCiMjI0FuYWx5dGljYWwgUXVlcnkgSTogVG9wIGZpdmUgc2FsZXMgcmVwcyB3aXRoIHRoZSBtb3N0IHNhbGVzIGJyb2tlbiBkb3duIGJ5IHllYXIuIFNvLCBmb3IgZWFjaCB5ZWFyIGZvciB3aGljaCB0aGVyZSBpcyBkYXRhLCB0aGUgdG9wIGZpdmUgcmVwcyBmb3IgdGhhdCB5ZWFyLiBUaGluayBhYm91dCBob3cgeW91IHdvdWxkIGJlc3QgcHJlc2VudCB0aGlzIGluZm9ybWF0aW9uIHNvIGl0IGlzIGVhc2lseSB1c2FibGUgYnkgYW4gYW5hbHlzdCBvciBtYW5hZ2VyDQpgYGB7ciBBbmFseXRpY2FsIFF1ZXJ5IDF9DQpxdWVyeTEgPC0gIg0KV0lUSCByYW5rZWRfcmVwcyBBUyAoDQogICAgU0VMRUNUIA0KICAgICAgICB5ZWFyLA0KICAgICAgICByZXBOYW1lLA0KICAgICAgICBTVU0odG90YWxTb2xkKSBBUyB0b3RhbF9zYWxlcywNCiAgICAgICAgUk9XX05VTUJFUigpIE9WRVIgKFBBUlRJVElPTiBCWSB5ZWFyIE9SREVSIEJZIFNVTSh0b3RhbFNvbGQpIERFU0MpIEFTIHNhbGVzX3JhbmsNCiAgICBGUk9NIA0KICAgICAgICByZXBfZmFjdHMgDQogICAgR1JPVVAgQlkgDQogICAgICAgIHllYXIsIHJlcE5hbWUNCikNClNFTEVDVCANCiAgICB5ZWFyLA0KICAgIHJlcE5hbWUsDQogICAgdG90YWxfc2FsZXMNCkZST00gDQogICAgcmFua2VkX3JlcHMNCldIRVJFIA0KICAgIHNhbGVzX3JhbmsgPD0gNQ0KT1JERVIgQlkgDQogICAgeWVhciwgdG90YWxfc2FsZXMgREVTQzsNCg0KIg0KcmVzdWx0MSA8LSBkYkdldFF1ZXJ5KGNvbiwgcXVlcnkxKQ0KIyBGb3JtYXR0aW5nIHRoZSBkYXRhIGZvciBwcmVzZW50YXRpb24NCiBkYXRhdGFibGUocmVzdWx0MSwgDQogICAgICAgICAgb3B0aW9ucyA9IGxpc3QoDQogICAgICAgICAgICBzdHJpcGVDbGFzc2VzID0gYygnb2RkJywgJ2V2ZW4nKSwgICMgQXBwbHkgYWx0ZXJuYXRpbmcgcm93IGNvbG9ycw0KICAgICAgICAgICAgcGFnaW5nID0gVFJVRSwgICAgICAgICAgICAgICAgICAgICAgIyBFbmFibGUgcGFnaW5hdGlvbiBpZiBuZWVkZWQNCiAgICAgICAgICAgIHNlYXJjaGluZyA9IFRSVUUgICAgICAgICAgICAgICAgICAgICMgRW5hYmxlIHNlYXJjaCBmdW5jdGlvbmFsaXR5DQogICAgICAgICAgKQ0KKQ0KDQoNCmBgYA0KDQojIyNBbmFseXRpY2FsIFF1ZXJ5IElJOiBUb3RhbCBzb2xkIHBlciBwcm9kdWN0IHBlciBxdWFydGVyLiBUaGluayBhYm91dCBob3cgdG8gYmVzdCBkaXNwbGF5IHRoaXMgaW5mb3JtYXRpb24uDQpgYGB7ciBBbmFseXRpY2FsIFF1ZXJ5IElJfQ0KQVEyIDwtICINClNFTEVDVCANCiAgICBwcm9kdWN0TmFtZSwNCiAgICBxdWFydGVyLA0KICAgIFNVTSh0b3RhbFVuaXRzU29sZCkgQVMgdG90YWxVbml0c1NvbGQNCkZST00gcHJvZHVjdF9mYWN0cyANCkdST1VQIEJZIHByb2R1Y3ROYW1lLCBxdWFydGVyDQpPUkRFUiBCWQ0KICAgIHByb2R1Y3ROYW1lLA0KICAgIHF1YXJ0ZXI7DQoiDQojIEV4ZWN1dGUgdGhlIHF1ZXJ5IGFuZCBzdG9yZSB0aGUgcmVzdWx0DQpyZXN1bHRJSSA8LSBkYkdldFF1ZXJ5KGNvbiwgQVEyKQ0KDQojIEFycmFuZ2UgdGhlIHJlc3VsdCBieSBwcm9kdWN0TmFtZSwgYW5kIHF1YXJ0ZXINCnJlc3VsdElJIDwtIGFycmFuZ2UocmVzdWx0SUksIHByb2R1Y3ROYW1lLCBxdWFydGVyKQ0KDQpkYXRhdGFibGUocmVzdWx0SUksIA0KICAgICAgICAgIG9wdGlvbnMgPSBsaXN0KA0KICAgICAgICAgICAgcGFnaW5nID0gVFJVRSwgICAgICAgICAjIEVuYWJsZSBwYWdpbmF0aW9uDQogICAgICAgICAgICBzZWFyY2hpbmcgPSBUUlVFLCAgICAgICMgRW5hYmxlIHNlYXJjaCBmdW5jdGlvbmFsaXR5DQogICAgICAgICAgICBvcmRlcmluZyA9IFRSVUUgICAgICAgIyBFbmFibGUgY29sdW1uIHNvcnRpbmcNCiAgICAgICAgICApLA0KICAgICAgICAgIGNhcHRpb24gPSAiQXZlcmFnZSBTYWxlcyBwZXIgU2FsZXMgUmVwIE92ZXIgdGhlIFllYXJzIg0KKQ0KDQoNCg0KYGBgDQoNCg0KIyMjIEFuYWx5dGljYWwgUXVlcnkgSUlJOiBOdW1iZXIgb2YgdW5pdHMgc29sZCBwZXIgcHJvZHVjdCBwZXIgcmVnaW9uLiBTaG93IHRoZSByZXN1bHQgaW4gYSBsaW5lIGdyYXBoIHZpc3VhbGl6YXRpb24NCmBgYHtyIEFuYWx5dGljYWxfUXVlcnlfSUlJLCB3YXJuaW5nPUZBTFNFfQ0KDQojIEV4ZWN1dGUgdGhlIHF1ZXJ5IGFuZCBzdG9yZSB0aGUgcmVzdWx0DQpBUTMgPC0gIg0KU0VMRUNUIA0KICAgIHByb2R1Y3ROYW1lLA0KICAgIHRlcnJpdG9yeSwNCiAgICBTVU0odG90YWxVbml0c1NvbGQpIEFTIHRvdGFsVW5pdHNTb2xkDQpGUk9NIHByb2R1Y3RfZmFjdHMgDQpHUk9VUCBCWSBwcm9kdWN0TmFtZSwgdGVycml0b3J5DQoiDQpyZXN1bHRJSUkgPC0gZGJHZXRRdWVyeShjb24sIEFRMykNCg0KIyBBcnJhbmdlIHRoZSByZXN1bHQgDQpyZXN1bHRJSUkgPC0gYXJyYW5nZShyZXN1bHRJSUksIHByb2R1Y3ROYW1lLCB0ZXJyaXRvcnkpDQoNCiMgRm9ybWF0dGluZyB0aGUgZGF0YSBmb3IgcHJlc2VudGF0aW9uDQpyZXN1bHQgPC0gcmVzdWx0SUlJICU+JQ0KICBrYWJsZSgiaHRtbCIpICU+JQ0KICBrYWJsZV9zdHlsaW5nKGJvb3RzdHJhcF9vcHRpb25zID0gInN0cmlwZWQiLCBmdWxsX3dpZHRoID0gRkFMU0UpDQoNCnJlc3VsdA0KDQoNCmxpYnJhcnkocGxvdGx5KQ0KDQojIEFzc3VtaW5nICdyZXN1bHQzJyBpcyB5b3VyIGRhdGEgZnJhbWUgY29udGFpbmluZyB0aGUgcXVlcnkgcmVzdWx0DQoNCiMgQ3JlYXRlIHRoZSBsaW5lIHBsb3QNCmxpbmVfcGxvdCA8LSBwbG90X2x5KHJlc3VsdElJSSwgeCA9IH50ZXJyaXRvcnksIHkgPSB+dG90YWxVbml0c1NvbGQsIGNvbG9yID0gfnByb2R1Y3ROYW1lLCB0eXBlID0gJ3NjYXR0ZXInLCBtb2RlID0gJ2xpbmVzK21hcmtlcnMnKSAlPiUNCiAgbGF5b3V0KHRpdGxlID0gIlRvdGFsIFVuaXRzIFNvbGQgYnkgVGVycml0b3J5IiwNCiAgICAgICAgIHhheGlzID0gbGlzdCh0aXRsZSA9ICJUZXJyaXRvcnkiKSwNCiAgICAgICAgIHlheGlzID0gbGlzdCh0aXRsZSA9ICJUb3RhbCBVbml0cyBTb2xkIiksDQogICAgICAgICBsZWdlbmQgPSBsaXN0KG9yaWVudGF0aW9uID0gImgiKSkNCg0KIyBQcmludCB0aGUgbGluZSBwbG90DQpsaW5lX3Bsb3QNCg0KYGBgDQoNCg0KDQojIyNBbmFseXRpY2FsIFF1ZXJ5IElWOiBBdmVyYWdlIHNhbGVzIHBlciBzYWxlcyByZXAgb3ZlciB0aGUgeWVhcnMuIFNob3cgdGhlIHJlc3VsdCBpbiBhIGxpbmUgZ3JhcGggdmlzdWFsaXphdGlvbi4NCmBgYHtyIEFuYWx5dGljYWwgUXVlcnkgSVZ9DQpsaWJyYXJ5KERUKQ0KIyBFeGVjdXRlIHRoZSBTUUwgcXVlcnkgYW5kIGZldGNoIHRoZSByZXN1bHQNCkFRNCA8LSAiDQpTRUxFQ1QgDQogICAgcmYucmVwTmFtZSwNCiAgICByZi55ZWFyLA0KICAgIEFWRyhyZi50b3RhbFNvbGQpIEFTIGF2Z19zYWxlcw0KRlJPTSANCiAgICByZXBfZmFjdHMgcmYNCkpPSU4gDQogICAgcHJvZHVjdF9mYWN0cyBwZiBPTiByZi5wcm9kdWN0SUQgPSBwZi5wcm9kdWN0SUQNCkdST1VQIEJZIA0KICAgIHJmLnJlcE5hbWUsIHJmLnllYXI7DQoiDQpyZXN1bHRJViA8LSBkYkdldFF1ZXJ5KGNvbiwgQVE0KQ0KDQojIERpc3BsYXkgdGhlIHJlc3VsdCBpbiB0YWJsZSBmb3JtYXQNCmRhdGF0YWJsZShyZXN1bHRJViwgDQogICAgICAgICAgb3B0aW9ucyA9IGxpc3QoDQogICAgICAgICAgICBwYWdpbmcgPSBUUlVFLCAgICAgICAgICMgRW5hYmxlIHBhZ2luYXRpb24NCiAgICAgICAgICAgIHNlYXJjaGluZyA9IFRSVUUsICAgICAgIyBFbmFibGUgc2VhcmNoIGZ1bmN0aW9uYWxpdHkNCiAgICAgICAgICAgIG9yZGVyaW5nID0gVFJVRSAgICAgICAjIEVuYWJsZSBjb2x1bW4gc29ydGluZw0KICAgICAgICAgICksDQogICAgICAgICAgY2FwdGlvbiA9ICJBdmVyYWdlIFNhbGVzIHBlciBTYWxlcyBSZXAgT3ZlciB0aGUgWWVhcnMiDQopDQpsaWJyYXJ5KGdncGxvdDIpDQoNCiMgUGxvdCB0aGUgbGluZSBncmFwaA0KZ2dwbG90KHJlc3VsdElWLCBhZXMoeCA9IHllYXIsIHkgPSBhdmdfc2FsZXMsIGdyb3VwID0gcmVwTmFtZSwgY29sb3IgPSByZXBOYW1lKSkgKw0KICBnZW9tX2xpbmUoKSArDQogIGdlb21fcG9pbnQoKSArDQogIGxhYnModGl0bGUgPSAiQXZlcmFnZSBTYWxlcyBwZXIgU2FsZXMgUmVwIE92ZXIgdGhlIFllYXJzIiwNCiAgICAgICB4ID0gIlllYXIiLA0KICAgICAgIHkgPSAiQXZlcmFnZSBTYWxlcyIpICsNCiAgdGhlbWVfbWluaW1hbCgpICsNCiAgdGhlbWUobGVnZW5kLnBvc2l0aW9uID0gImJvdHRvbSIpICsNCiAgc2NhbGVfY29sb3JfdmlyaWRpc19kKCkNCg0KZGJEaXNjb25uZWN0KGNvbikNCg0KYGBgDQojIyBDb25jbHVzaW9uDQoNClRoaXMgcmVwb3J0IHByb3ZpZGVzIHZhbHVhYmxlIGluc2lnaHRzIGludG8gb3VyIHNhbGVzIHBlcmZvcm1hbmNlLCBoaWdobGlnaHRpbmcgdHJlbmRzIGFuZCBwYXR0ZXJucyB0aGF0IGNhbiBpbmZvcm0gc3RyYXRlZ2ljIGRlY2lzaW9uLW1ha2luZy4gQnkgdW5kZXJzdGFuZGluZyBvdXIgc2FsZXMgZGF0YSBtb3JlIGNvbXByZWhlbnNpdmVseSwgd2UgY2FuIGlkZW50aWZ5IG9wcG9ydHVuaXRpZXMgZm9yIGdyb3d0aCBhbmQgb3B0aW1pemUgb3VyIHNhbGVzIHN0cmF0ZWdpZXMuDQoNCg0KDQoNCg0K